1917. Leetcodify Friends Recommendations (Hard).sql

WITH all_recommendations AS (
SELECTl1.user_idAS user_id1,l2.user_idAS user_id2,COUNT(l1.song_id) AS listened_songs
FROM listens_1917 l1
INNER JOIN listens_1917 l2
ONl1.user_id<>l2.user_idAND
l1.song_id=l2.song_idAND
l1.day=l2.day
GROUP BYl1.user_id,l2.user_id
HAVINGCOUNT(l1.song_id)>=3
),
friends AS (
SELECT user1_id,user2_id 
FROM friendship_1917
UNION
SELECT user2_id,user1_id 
FROM friendship_1917
)
SELECTr.user_id1,r.user_id2
FROM all_recommendations r
LEFT JOIN friends f ONf.user1_id=r.user_id1ANDf.user2_id=r.user_id2
WHEREf.user1_id IS NULL;